﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Common;
using DreamBuilder.Core.DAO;
using DreamBuilder.Core;
using DreamBuilder.Core.Message;
using System.Data.Common;
using System.Data.OracleClient;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;

namespace CommonModule
{
    public class DataHelper : DataAccessBase
    {
        public DataHelper()
        {
            if (this.DBHelper == null)
            {
                this.DBHelper = DBHelperFactory.CreateInstance(DbHelperName.Business);
            }
        }

        /// <summary>
        /// 保存数据(强类型DataSet)
        /// </summary>
        /// <param name="datatables"></param>
        /// <returns></returns>
        public void InsertData(DataSet ds)
        {
            try
            {
                DataSet dsOut = new DataSet();
                List<string> tableNames = new List<string>();
                List<string> primaryKeys = new List<string>();
                List<IKeyGen> genlist = new List<IKeyGen>();

                foreach (DataTable dt in ds.Tables)
                {
                    tableNames.Add(dt.TableName);
                    primaryKeys.Add(dt.PrimaryKey[0].ColumnName);
                    genlist.Add(null);
                }
                SaveData(out dsOut, ds, tableNames.ToArray(), primaryKeys.ToArray(), genlist.ToArray());
                DataSet ds1 = dsOut;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页查询函数
        /// </summary>
        /// <param name="select"></param>
        /// <param name="from"></param>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <param name="ascOrDesc"></param>
        /// <param name="intPageIndex"></param>
        /// <param name="intPageSize"></param>
        /// <param name="recordCount"></param>
        /// <param name="dictionary">改写查询结果显示名称的字典</param>
        /// <returns></returns>
        public System.Data.DataTable SelectListPaged(string select, string from, string where, string orderBy, string ascOrDesc, int intPageIndex, int intPageSize, out int recordCount, Dictionary<string, string> dictionary)
        {
            if (!(this.DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
            OracleParameter v_cur = new OracleParameter("v_cur", OracleType.Cursor);
            v_cur.Direction = ParameterDirection.Output;

            OracleParameter p_recordCount = new OracleParameter("RecordCount", OracleType.Int32);
            p_recordCount.Direction = ParameterDirection.Output;

            OracleParameter p_pageIndex = new OracleParameter("PageIndex", OracleType.Int32);
            p_pageIndex.Value = intPageIndex;
            p_pageIndex.Direction = ParameterDirection.InputOutput;

            OracleParameter p_pageSize = new OracleParameter("PageSize", OracleType.Int32);
            p_pageSize.Value = intPageSize;
            p_pageSize.Direction = ParameterDirection.InputOutput;

            DbParameter[] parameters = new DbParameter[]
            {
                new  OracleParameter ("SelectStatement",select),
                new  OracleParameter ("FromStatement",from),
                new  OracleParameter ("WhereStatement",where),
                new  OracleParameter ("OrderByExpression",orderBy),
                new  OracleParameter ("AscOrDesc",ascOrDesc),
                p_pageSize,
                p_pageIndex,
                p_recordCount,
                v_cur

            };

            DataTable dtInfo = DBHelper.RunProcedureDataTable("proc_qurey", parameters);
            recordCount = Convert.ToInt32(p_recordCount.Value);
            foreach (string key in dictionary.Keys)
            {
                string aliasName = "";
                dictionary.TryGetValue(key, out aliasName);
                dtInfo.Columns[key].ColumnName = aliasName;
            }
            return dtInfo;

        }

        public void UploadAttachment(string dbHelperName, string attachmentId, string objectTableName, string objectId, string attachmentName, int attachmentSize, string documentType, System.IO.Stream attachmentContent, string fileTypeId, string fileUrl, string createuserId, string remark)
        {
            try
            {
                IDBHelper helper = DBHelperFactory.CreateInstance(dbHelperName);
                using (OracleConnection conn = new OracleConnection(helper.database.ConnectionString))
                {
                    conn.Open();
                    OracleTransaction tran = conn.BeginTransaction();
                    //这个事务是必须要使用的，否则无法提交成功
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = tran;
                        ///先用一个empty_blob()，填充一个空块
                        cmd.CommandText = string.Format("INSERT INTO P_BT_ATTACHMENTS (ATTACHMENTID,OBJECTTABLENAME,OBJECTID,ATTACHMENTNAME,UPLOADTIME,ATTACHMENTSIZE,DOCUMENTTYPE,ATTACHMENTCONTENT,FILETYPEID,FILEURL,CREATEUSERID,REMARK) VALUES(" +
                        "'{0}','{1}','{2}','{3}',sysdate,{4},'{5}',empty_blob(),'{6}','{7}','{8}','{9}')", attachmentId, objectTableName, objectId, attachmentName, attachmentSize, documentType, fileTypeId, fileUrl, createuserId, remark);
                        cmd.ExecuteNonQuery();

                        ///再读取，更新
                        cmd.CommandText = string.Format("SELECT ATTACHMENTCONTENT FROM P_BT_ATTACHMENTS WHERE ATTACHMENTID='{0}' FOR UPDATE", attachmentId);
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                OracleLob lob = (OracleLob)reader.GetOracleLob(0);
                                int bufferSize = 4096;
                                byte[] buffer = new byte[bufferSize];
                                int size = 0;
                                while ((size = attachmentContent.Read(buffer, 0, bufferSize)) > 0)
                                {
                                    lob.Write(buffer, 0, size);
                                }
                            }
                            reader.Close();
                        }
                        tran.Commit();//提交事务
                        conn.Close();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        public DataTable GetAttachmentIds(List<string> objectIds, string dbHelperName)
        {
            try
            {
                IDBHelper helper = DBHelperFactory.CreateInstance(dbHelperName);
                StringBuilder keys = new StringBuilder("");
                foreach (string s in objectIds)
                {
                    keys.Append("'" + s + "',");
                }
                if (keys.Length > 0)
                {
                    keys = keys.Remove(keys.Length - 1, 1);
                }
                string sql = "select ATTACHMENTID, ATTACHMENTNAME, ATTACHMENTSIZE from  P_BT_ATTACHMENTS" + " where OBJECTID in (" + keys + ")";
                DataSet ds = helper.ExecuteDataSet(sql);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetDbHelperNameByArchiveType(string archiveTypeId)
        {
            string dbHelperName = string.Empty;
            switch (archiveTypeId)
            {
                case "1":
                    dbHelperName = "DocumentFile";
                    break;
                case "2": //光盘类 E
                    dbHelperName = "CDKind";
                    break;
                case "3": //科学技术研究类 F
                    dbHelperName = "Technology";
                    break;
                case "4": //建设项目类 G
                    dbHelperName = "BuildProjectFile";
                    break;
                case "5": //设备仪器类 H
                    dbHelperName = "Equipment";
                    break;
                case "61": //会计凭证 I01
                case "62": //会计账簿 I02
                case "63": //财务报告 I03
                case "64": //其他 I04
                    dbHelperName = "AccountFile";
                    break;
                case "71": //勘探综合
                case "72": //勘探单井
                    dbHelperName = "oilsearch";
                    break;
                case "81": //声像
                case "82": //声像
                case "83": //声像
                    dbHelperName = "audiofile";
                    break;
                case "91": //荣誉 R01
                    dbHelperName = "RealKind";
                    break;
                case "101": //文书 W 历史文件
                    dbHelperName = "DocumentFile";
                    break;
                case "102": //文书 W 历史文件
                    dbHelperName = "DocumentFile";
                    break;
                case "7101": //勘探综合 历史文件
                    dbHelperName = "oilsearch";
                    break;
                case "7102": //勘探综合 历史文件
                    dbHelperName = "oilsearch";
                    break;
                default:
                    throw new Exception("no such catalogId");
            }
            return dbHelperName;
        }
    }
}
